package cn.edu.buaa.cnsatm.traffic_big_data_server.aviation.dao;

import cn.edu.buaa.cnsatm.traffic_big_data_server.aviation.model.AirportWeatherModel;
import cn.edu.buaa.cnsatm.traffic_big_data_server.highway.model.ImageService;
import cn.edu.buaa.cnsatm.traffic_big_data_server.util.JDBCUtil;
import cn.edu.buaa.cnsatm.traffic_big_data_server.util.StringUtil;
import org.joda.time.DateTime;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class AirportDAO {

    /**
     * @author: CHEN LI, LX
     * @className: AirportDao
     * @packageName: Dao
     * @description: 航空气象的影响(修改，获取了真实数据，只返回异常的情况)
     * @data: 2019-10-19, 2019-10-24
     **/
    public static ResultSet getAirportWeatherEffects(String top_num){
        String basesql = "SELECT airport_name, effects, datetime FROM airport_weather_effect WHERE effects != '机场状况正常' AND effects != '未知' ORDER BY datetime DESC LIMIT " + top_num;
        Statement stmt = null;
        ResultSet resultSet = null;
        try {
            stmt = JDBCUtil.databaseConnecttion.createStatement();//创建数据库查询实体
            resultSet = stmt.executeQuery(basesql);//执行Sql查询语句
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return resultSet;
    }

    public static ResultSet getAllAirportCodeAndName(){
        String basesql = "SELECT iata_code, gps_code, cn_name FROM china_airport where iata_code is not null";
        Statement stmt = null;
        ResultSet resultSet = null;
        try {
            stmt = JDBCUtil.databaseConnecttion.createStatement();//创建数据库查询实体
            resultSet = stmt.executeQuery(basesql);//执行Sql查询语句
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return resultSet;
    }
    public static String insertAirportWeather(AirportWeatherModel airportWeather){
        /**
         * @author LX
         * @description 将爬到的气象影响机场数据存到数据库中
         * @since 2019-10-24
         */
        int insertNum = 0;
//        String sql = "INSERT into airport_weather_effect (airport_name, effects, temp, weather, vision, cloud_height, wind_direction, wind_level, wind_speed, datetime) " +
//                "values('" + airportName + "', '" + effects + "', '"  + temp + "', '" + weather + "', '" +
//                vision + "', '" + cloudHeight + "', '" + windDirection + "', '" + windLevel + "', '" +
//                windSpeed + "', '" + datetime + "' )";
        String sql = "INSERT into airport_weather_effect " +
                "(airport_name, effects, datetime, cloud, weather, wind_speed, temperature, visibility) " +
                "values('" + airportWeather.getAirport_name() + "', '" + airportWeather.getEffects() + "', '"  + airportWeather.getDatetime() +
                "','" + airportWeather.getCloud() + "','" + airportWeather.getWeather() + "'," + airportWeather.getWind_speed() + ", " +
                airportWeather.getTemperature() + ", " + airportWeather.getVisibility() + " );";
        //System.out.println(sql);
//        Statement stmt = null;
//        try {
//            stmt = JDBCUtil.databaseConnecttion.createStatement();//创建数据库查询实体
//            insertNum = stmt.executeUpdate(sql);//执行Sql查询语句
//            stmt.close();
//            //System.out.print(insertNum);
//        } catch (SQLException e) {
//            //e.printStackTrace();
//        }
        return sql;
    }
}

